﻿using Freshbox2._0.BLL;
using Freshbox2._0.MODEL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Web;
using System.Web.Mvc;
using PagedList;
using System.Text;

namespace Freshbox2._0.UI.Controllers
{
    public class ReplenishMinNumController : BaseController<log>
    {
        //店铺商品上架排行
        // GET: /ReplenishMinNum/

        public override BLL.BaseBll<log> bll
        {
            get
            {
                return new LogBll();
            }

        }

        public class ReplenishModel
        {
            public string waresName { get; set; }
            public string waresCode { get; set; }
            public string storeName { get; set; }
            public int rate { get; set; }
            public int upNum { get; set; }
            public int logRate { get; set; }
            public int? logNum { get; set; }
        }

        WaresDetailsBll waresDetailBll = new WaresDetailsBll();

        WaresBll waresBll = new WaresBll();

        StoreBll storeBll = new StoreBll();

        //int page, int rows,
        public ActionResult GetNoticeEf(log model, string sort, string order, string date1, string date2)
        {
            var listReplenishModel = new List<ReplenishModel>();

            var listOrderReplenishModel = new List<ReplenishModel>();
            //默认情况下
            if (model.storeCode == null && string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2))
            {
                DateTime datetime1 = DateTime.Now;
                DateTime datetime2 = DateTime.Now.Date.AddDays(-6);
                Expression<Func<log, bool>> condition = x => x.storeCode == 10010 && x.logTime >= datetime2 && x.logTime <= datetime1;
                var storeList = storeBll.Search(x => x.storeid == 10010).Select(x => new { x.storeName }).ToList();
                //DateTime datetime1 = DateTime.Parse(date1);
                //DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                //1. 查询出店铺里的商品   
                var storeWareList = waresDetailBll.Search(x => x.storeid == 10010).Select(x => new { x.waresCode, x.wares.waresName }).ToList();

                foreach (var itemWares in storeWareList)
                {
                    //根据商品查询操作记录对应的
                    var rateList = bll.Search(condition).Where(x => x.waresCode == itemWares.waresCode && x.logType == 1).ToList();  //上架频次
                    //忽略下架的商品
                    var LogList = bll.Search(condition).Where(x => x.waresCode == itemWares.waresCode && x.logType == 1).GroupBy(x => x.waresCode).Select(x => new
                    {
                        x.FirstOrDefault().waresCode,
                        waresName = x.FirstOrDefault().wares.waresName,
                        before = x.Sum(y => y.beforeUpdate),
                        after = x.Sum(y => y.afterUpdate),
                    }).ToList();
                    if (LogList.Count() != 0)
                    {
                        ReplenishModel replenishModel = new ReplenishModel();
                        replenishModel.waresCode = itemWares.waresCode;
                        replenishModel.waresName = itemWares.waresName;
                        replenishModel.storeName = storeList.FirstOrDefault().storeName;
                        replenishModel.rate = rateList.Count();
                        replenishModel.upNum = Math.Abs(Convert.ToInt32(LogList.FirstOrDefault().before) - Convert.ToInt32(LogList.FirstOrDefault().after));
                        listReplenishModel.Add(replenishModel);
                    }
                    else
                    {
                        ReplenishModel replenishModel = new ReplenishModel();
                        replenishModel.waresCode = itemWares.waresCode;
                        replenishModel.waresName = itemWares.waresName;
                        replenishModel.rate = rateList.Count();
                        replenishModel.storeName = storeList.FirstOrDefault().storeName;
                        replenishModel.upNum = 0;
                        listReplenishModel.Add(replenishModel);
                    }

                }
            }
            //根据店铺查询
            if (model.storeCode != null && string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2))
            {
                DateTime datetime1 = DateTime.Now;
                DateTime datetime2 = DateTime.Now.Date.AddDays(-6);
                Expression<Func<log, bool>> condition = x => x.storeCode == model.storeCode && x.logTime >= datetime2 && x.logTime <= datetime1;
                var storeList = storeBll.Search(x => x.storeid == model.storeCode).Select(x => new { x.storeName }).ToList();

                //1. 查询出店铺里的商品   
                var storeWareList = waresDetailBll.Search(x => x.storeid == model.storeCode).Select(x => new { x.waresCode, x.wares.waresName }).ToList();

                foreach (var itemWares in storeWareList)
                {
                    //根据商品查询操作记录对应的
                    var rateList = bll.Search(condition).Where(x => x.waresCode == itemWares.waresCode && x.logType == 1).ToList();
                    var LogList = bll.Search(condition).Where(x => x.waresCode == itemWares.waresCode).GroupBy(x => x.waresCode).Select(x => new
                    {
                        x.FirstOrDefault().waresCode,
                        waresName = x.FirstOrDefault().wares.waresName,
                        before = x.Sum(y => y.beforeUpdate),
                        after = x.Sum(y => y.afterUpdate),
                    }).ToList();
                    if (LogList.Count() != 0)
                    {
                        ReplenishModel replenishModel = new ReplenishModel();
                        replenishModel.waresCode = itemWares.waresCode;
                        replenishModel.waresName = itemWares.waresName;
                        replenishModel.storeName = storeList.FirstOrDefault().storeName;
                        replenishModel.rate = rateList.Count();
                        replenishModel.upNum = Math.Abs(Convert.ToInt32(LogList.FirstOrDefault().before) - Convert.ToInt32(LogList.FirstOrDefault().after));
                        listReplenishModel.Add(replenishModel);
                    }
                    else
                    {
                        ReplenishModel replenishModel = new ReplenishModel();
                        replenishModel.waresCode = itemWares.waresCode;
                        replenishModel.waresName = itemWares.waresName;
                        replenishModel.rate = rateList.Count();
                        replenishModel.storeName = storeList.FirstOrDefault().storeName;
                        replenishModel.upNum = 0;
                        listReplenishModel.Add(replenishModel);
                    }

                }
            }

            //根据店铺 和时间 查询
            if (model.storeCode != null && date1 != "" && date2 != "" && date1 != null && date2 != null)
            {
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                Expression<Func<log, bool>> condition = x => x.storeCode == model.storeCode && x.logTime >= datetime1 && x.logTime <= datetime2;
                var storeList = storeBll.Search(x => x.storeid == model.storeCode).Select(x => new { x.storeName }).ToList();
                //1. 查询出店铺里的商品   
                var storeWareList = waresDetailBll.Search(x => x.storeid == model.storeCode).Select(x => new { x.waresCode, x.wares.waresName }).ToList();
                foreach (var itemWares in storeWareList)
                {
                    //根据商品查询操作记录对应的
                    var rateList = bll.Search(condition).Where(x => x.waresCode == itemWares.waresCode && x.logType == 1).ToList();
                    var LogList = bll.Search(condition).Where(x => x.waresCode == itemWares.waresCode).GroupBy(x => x.waresCode).Select(x => new
                    {
                        x.FirstOrDefault().waresCode,
                        waresName = x.FirstOrDefault().wares.waresName,
                        before = x.Sum(y => y.beforeUpdate),
                        after = x.Sum(y => y.afterUpdate),
                    }).ToList();
                    if (LogList.Count() != 0)
                    {
                        ReplenishModel replenishModel = new ReplenishModel();
                        replenishModel.waresCode = itemWares.waresCode;
                        replenishModel.waresName = itemWares.waresName;
                        replenishModel.storeName = storeList.FirstOrDefault().storeName;
                        replenishModel.rate = rateList.Count();
                        replenishModel.upNum = Math.Abs(Convert.ToInt32(LogList.FirstOrDefault().before) - Convert.ToInt32(LogList.FirstOrDefault().after));
                        listReplenishModel.Add(replenishModel);
                    }
                    else
                    {
                        ReplenishModel replenishModel = new ReplenishModel();
                        replenishModel.waresCode = itemWares.waresCode;
                        replenishModel.waresName = itemWares.waresName;
                        replenishModel.rate = rateList.Count();
                        replenishModel.storeName = storeList.FirstOrDefault().storeName;
                        replenishModel.upNum = 0;
                        listReplenishModel.Add(replenishModel);
                    }

                }
            }


            listOrderReplenishModel = listReplenishModel.OrderBy(x => x.upNum).ToList();


            //if (sort == "avgUpNum" && order == "desc")    .ToPagedList(page, rows).
            //{
            //    listOrderLogModel = listLogModel.OrderByDescending(x => x.avgUpNum).ToPagedList(page, rows).ToList(); //pageNum 页码数    pagesize  每页显示几条数据
            //}
            //if (sort == "avgUpNum" && order == "asc")
            //{
            //    listOrderLogModel = listLogModel.OrderBy(x => x.avgUpNum).ToPagedList(page, rows).ToList(); //pageNum 页码数    pagesize  每页显示几条数据
            //}
            //if (sort == "rate" && order == "desc")
            //{
            //    listOrderLogModel = listLogModel.OrderByDescending(x => x.rate).ToPagedList(page, rows).ToList();
            //}
            //if (sort == "rate" && order == "asc")
            //{
            //    listOrderLogModel = listLogModel.OrderBy(x => x.rate).ToPagedList(page, rows).ToList();
            //}

            return Json(new { rows = listOrderReplenishModel, total = listReplenishModel.Count() });

        }

        List<ReplenishModel> ReplenishList = new List<ReplenishModel>();

        public ActionResult GetNoticeBySql(log model, string sort, string order, string date1, string date2)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT COUNT(*) AS logRate ,SUM(beforeUpdate-afterUpdate) AS logNum,log.waresCode ,wares.waresName FROM log  INNER JOIN wares on log.waresCode=wares.waresCode WHERE  ");
            //默认情况   默认查询本天的所有记录  
            if (string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2) && model.storeCode == null)
            {
                DateTime todayNow = DateTime.Now.Date;
                DateTime midnight = DateTime.Now.Date.AddDays(-14);              
                // logTime BETWEEN '2018-07-07 09:31:26' AND '2018-07-21 09:32:10'  GROUP BY waresCode ORDER BY logNum DESC
                sql.Append("  logTime between  '" + midnight + "' AND '" + todayNow + "' ");
            }
            //根据店铺查询
            if (model.storeCode != null && string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2))
            {
                DateTime todayNow = DateTime.Now.Date;
                DateTime midnight = DateTime.Now.Date.AddDays(-14);
                // logTime BETWEEN '2018-07-07 09:31:26' AND '2018-07-21 09:32:10'  GROUP BY waresCode ORDER BY logNum DESC
                sql.Append("  logTime between  '" + midnight + "' AND '" + todayNow + "'  and storeCode= '"+ model.storeCode +"'  ");
            }
            //根据店铺 和时间 查询
            if (model.storeCode != null && date1 != "" && date2 != "" && date1 != null && date2 != null)
            {
                DateTime datea = Convert.ToDateTime(date1).Date;
                DateTime dateb= Convert.ToDateTime(date2).Date;
                // logTime BETWEEN '2018-07-07 09:31:26' AND '2018-07-21 09:32:10'  GROUP BY waresCode ORDER BY logNum DESC
                sql.Append("  logTime between  '" + datea + "' AND '" + dateb + "'  and storeCode= '" + model.storeCode + "'  ");
            }
            //根据时间 查询
            if (model.storeCode == null && date1 != "" && date2 != "" && date1 != null && date2 != null)
            {
                DateTime datea = Convert.ToDateTime(date1).Date;
                DateTime dateb = Convert.ToDateTime(date2).AddDays(1);
                // logTime BETWEEN '2018-07-07 09:31:26' AND '2018-07-21 09:32:10'  GROUP BY waresCode ORDER BY logNum DESC
                sql.Append("  logTime between  '" + datea + "' AND '" + dateb + "'   ");
            }
            sql.Append(" GROUP BY waresCode ORDER BY "+sort+" "+order+"  ");
            var list = bll.SearchBySQL<ReplenishModel>(sql.ToString()).ToList();
            foreach (var item in list)
            {
                ReplenishModel viewModel = new ReplenishModel();
                viewModel.logNum = item.logNum;
                viewModel.logRate = item.logRate;
                viewModel.waresCode = item.waresCode;
                viewModel.waresName = item.waresName;
                ReplenishList.Add(viewModel);

            }
            return Json(new { rows = ReplenishList, total = ReplenishList.Count });
        }
    }
}
